﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TodoApp.EntityFrameworkCore;
using TodoApp.ResultModel;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;

namespace TodoApp.TMS_settlement.Charges
{
    public class TMS_ChargeRepository : DapperRepository<TodoAppDbContext>, ITransientDependency, ITMS_ChargeRepository
    {
        public TMS_ChargeRepository(IDbContextProvider<TodoAppDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        public async Task<int> CreateCharge(TMS_Charge tMS_Charge)
        {
            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"insert into TMS_Charge values(NEWID(),'YF202010080122','{tMS_Charge.Charge_Company}','{tMS_Charge.Charge_Settlement}','{tMS_Charge.Charge_Tonnage}','{tMS_Charge.Charge_Price}','{tMS_Charge.Charge_Amount}','{tMS_Charge.Charge_Date}','{tMS_Charge.Charge_Agent}','{tMS_Charge.Charge_Note}',0,'5B584655-4081-4B44-A63F-CF653AA78633',getdate(),1,getdate())", null, await GetDbTransactionAsync());
        }

        public async Task<int> DeleteCharge(string id)
        {
            var dbConnection = await GetDbConnectionAsync();
            var count = 0;
            var ids = id.Split(',');
            foreach (var item in ids)
            {
                var sql = $"update TMS_Charge set Charge_Delete='false' where Id='{item}'";
                count += await dbConnection.ExecuteAsync(sql, null,
                 await GetDbTransactionAsync());
            }
            return count;
        }

        public async Task<PageResponse<TMS_ChargeDto>> GetCharge(string name, string department, string position, string date, bool? state, int pageindex, int pagesize)
        {
            PageResponse<TMS_ChargeDto> response = new PageResponse<TMS_ChargeDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select a.*,b.Employees_Name as name from TMS_Charge a left join TMS_Employees b on a.Charge_CheckPeople=b.Id where Charge_Delete='true'";
            var list = await dbConnection.QueryAsync<TMS_ChargeDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageTotal = list.Count();
            if (name != null)
            {
                sql += $" and b.Employees_Name = '{name}'";
            }
            if (department != null)
            {
                sql += $" and a.Charge_Agent = '%{department}%'";
            }
            if (position != null)
            {
                sql += $" and a.Charge_Settlement = '{position}'";
            }
            if (date != null)
            {
                sql += $" and a.Charge_Date = '{date}'";
            }
            if (state != null)
            {
                sql += $" and a.Charge_CheckState = '{state}'";
            }
            sql += $" order by a.Charge_DateTime offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await dbConnection.QueryAsync<TMS_ChargeDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageData = lists.ToList();
            return response;
        }

        public async Task<int> UpdateCharge(TMS_Charge tMS_Charge)
        {
            throw new NotImplementedException();
        }
    }
}
